package com.yys.excel.yysexcel.utils.easyPoi.excelExample;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.hutool.core.lang.Dict;
import cn.hutool.json.JSONUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

public class YlsfhzbExcelPoi {
    public static void main(String[] args) {
        excute("");
//        List<Dict> datas = getDatas();
//        Dict dict = countExcel(datas);
//        Set<String> strings = dict.keySet();
//        System.out.println();
    }

    //'安全围栏(警示锥筒围蔽)'
    public static void excute(String filePath) {
        Dict dict = Dict.create();
        List<Dict> datas = getDatas();
        // 合并单元格
        Dict countExcels = countExcel(datas);
        Set<String> keys = countExcels.keySet();
        int size = keys.size();
        dict.set("aqwlBzmkXh",++size);
        dict.set("aqwlJszXh",++size);

        Dict aqwl = Dict.create();
        aqwl.set("aqwlName","安全围栏(标准密扣式活动刚围栏)");
        aqwl.set("aqwlName2","安全围栏(警示锥筒围蔽)");

        dict.set("aqwl", aqwl);
        dict.set("datas", datas);

        //模板文件路径
        TemplateExportParams params = new TemplateExportParams("templates/excel/template/预览收方汇总表.xlsx");
        Workbook workbook = ExcelExportUtil.exportExcel(params, dict);

        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(filePath);

            int num = 9;
            for (String key : keys) {
                int count = countExcels.getInt(key);
                int lastRow = num + count;
                //CellRangeAddress(第几行开始，第几行结束，第几列开始，第几列结束)
                workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(num, lastRow-1, 0, 0));
                workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(num, lastRow-1, 1, 1));
                workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(num, lastRow-1, 2, 2));
                num = lastRow;
            }

            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取表格合并数
    public static Dict countExcel(List<Dict> show){
        Dict dict = Dict.create();
        for (Dict m : show) {
            String mapkey = m.getStr("mapkey");
            Integer num = dict.getInt(mapkey);
            if(num == null){
                dict.put(mapkey,1);
            }else {
                dict.put(mapkey,++num);
            }
        }

        //赋予序号值
        Dict num = Dict.create();
        int numIndex = 1;
        Set<String> keys = dict.keySet();
        for (String key : keys) {
            num.put(key,numIndex++);
        }

//        Collection<Dict> collect = show.stream().peek(m -> {
//            m.set("xhNum", num.getInt(m.getStr("mapkey")));
//        }).collect(Collectors.toList());
        show.stream().peek(m -> {
            m.set("xhNum", num.getInt(m.getStr("mapkey")));
        }).collect(Collectors.toList());
        return dict;
    }

    public static List<Dict> getDatas() {
        String s = "[\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"侧石\",\n" +
                "        \"QUANTITIES\": \"6\",\n" +
                "        \"mapkey\": \"侧石RN635*35*21cm花岗岩光面平方米\",\n" +
                "        \"sort\": \"01\",\n" +
                "        \"CZNAME\": \"花岗岩光面\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"RN6\",\n" +
                "        \"repairtypename\": \"利旧更换\",\n" +
                "        \"name\": \"拆除侧石\",\n" +
                "        \"formula\": \"1*6\",\n" +
                "        \"SPECOLOR\": \"35*35*21cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"侧石\",\n" +
                "        \"QUANTITIES\": \"6\",\n" +
                "        \"mapkey\": \"侧石RN635*35*21cm花岗岩光面平方米\",\n" +
                "        \"sort\": \"02\",\n" +
                "        \"CZNAME\": \"花岗岩光面\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"RN6\",\n" +
                "        \"repairtypename\": \"利旧更换\",\n" +
                "        \"name\": \"拆除侧石混凝土后座\",\n" +
                "        \"formula\": \"总米数*(0.1*0.15+0.1*0.2)\",\n" +
                "        \"SPECOLOR\": \"35*35*21cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"侧石\",\n" +
                "        \"QUANTITIES\": \"6\",\n" +
                "        \"mapkey\": \"侧石RN635*35*21cm花岗岩光面平方米\",\n" +
                "        \"sort\": \"03\",\n" +
                "        \"CZNAME\": \"花岗岩光面\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"RN6\",\n" +
                "        \"repairtypename\": \"利旧更换\",\n" +
                "        \"name\": \"余石外运(运距15km)\",\n" +
                "        \"formula\": \"总米数*(0.1*0.15+0.1*0.2)\",\n" +
                "        \"SPECOLOR\": \"35*35*21cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"侧石\",\n" +
                "        \"QUANTITIES\": \"6\",\n" +
                "        \"mapkey\": \"侧石RN635*35*21cm花岗岩光面平方米\",\n" +
                "        \"sort\": \"04\",\n" +
                "        \"CZNAME\": \"花岗岩光面\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"RN6\",\n" +
                "        \"repairtypename\": \"利旧更换\",\n" +
                "        \"name\": \"现浇C20砼侧石后座\",\n" +
                "        \"formula\": \"总米数*(0.1*0.15+0.1*0.2)\",\n" +
                "        \"SPECOLOR\": \"35*35*21cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"侧石\",\n" +
                "        \"QUANTITIES\": \"6\",\n" +
                "        \"mapkey\": \"侧石RN635*35*21cm花岗岩光面平方米\",\n" +
                "        \"sort\": \"05\",\n" +
                "        \"CZNAME\": \"花岗岩光面\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"RN6\",\n" +
                "        \"repairtypename\": \"利旧更换\",\n" +
                "        \"name\": \"利旧安装侧石(M10水泥砂浆砌筑)\",\n" +
                "        \"formula\": \"1*6\",\n" +
                "        \"SPECOLOR\": \"35*35*21cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"侧石\",\n" +
                "        \"QUANTITIES\": \"2\",\n" +
                "        \"mapkey\": \"侧石RN735*35*30cm花岗岩光面平方米\",\n" +
                "        \"sort\": \"01\",\n" +
                "        \"CZNAME\": \"花岗岩光面\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"RN7\",\n" +
                "        \"repairtypename\": \"新砌更换\",\n" +
                "        \"name\": \"拆除侧石\",\n" +
                "        \"formula\": \"1*2\",\n" +
                "        \"SPECOLOR\": \"35*35*30cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"侧石\",\n" +
                "        \"QUANTITIES\": \"4\",\n" +
                "        \"mapkey\": \"侧石RN735*35*30cm花岗岩光面平方米\",\n" +
                "        \"sort\": \"02\",\n" +
                "        \"CZNAME\": \"花岗岩光面\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"RN7\",\n" +
                "        \"repairtypename\": \"新砌更换\",\n" +
                "        \"name\": \"余石外运(运距15km)\",\n" +
                "        \"formula\": \"例如：新砌米数*0.3*0.15\",\n" +
                "        \"SPECOLOR\": \"35*35*30cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"侧石\",\n" +
                "        \"QUANTITIES\": \"2\",\n" +
                "        \"mapkey\": \"侧石RN735*35*30cm花岗岩光面平方米\",\n" +
                "        \"sort\": \"03\",\n" +
                "        \"CZNAME\": \"花岗岩光面\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"RN7\",\n" +
                "        \"repairtypename\": \"新砌更换\",\n" +
                "        \"name\": \"拆除侧石混凝土后座\",\n" +
                "        \"formula\": \"总米数*(0.1*0.15+0.1*0.2)\",\n" +
                "        \"SPECOLOR\": \"35*35*30cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"侧石\",\n" +
                "        \"QUANTITIES\": \"2\",\n" +
                "        \"mapkey\": \"侧石RN735*35*30cm花岗岩光面平方米\",\n" +
                "        \"sort\": \"05\",\n" +
                "        \"CZNAME\": \"花岗岩光面\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"RN7\",\n" +
                "        \"repairtypename\": \"新砌更换\",\n" +
                "        \"name\": \"现浇C20砼侧石后座\",\n" +
                "        \"formula\": \"总米数*(0.1*0.15+0.1*0.2)\",\n" +
                "        \"SPECOLOR\": \"35*35*30cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"侧石\",\n" +
                "        \"QUANTITIES\": \"2\",\n" +
                "        \"mapkey\": \"侧石RN735*35*30cm花岗岩光面平方米\",\n" +
                "        \"sort\": \"06\",\n" +
                "        \"CZNAME\": \"花岗岩光面\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"RN7\",\n" +
                "        \"repairtypename\": \"新砌更换\",\n" +
                "        \"name\": \"新砌侧石(M10水泥砂浆砌筑)\",\n" +
                "        \"formula\": \"1*2\",\n" +
                "        \"SPECOLOR\": \"35*35*30cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"沥青\",\n" +
                "        \"QUANTITIES\": \"4\",\n" +
                "        \"mapkey\": \"沥青R3003100x25X12cmAC-10平方米\",\n" +
                "        \"sort\": \"01\",\n" +
                "        \"CZNAME\": \"AC-10\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"R3003\",\n" +
                "        \"repairtypename\": \"刨铺人工\",\n" +
                "        \"name\": \"人工风炮凿挖沥青路面5cm外运 运距20km\",\n" +
                "        \"formula\": \"1*4\",\n" +
                "        \"SPECOLOR\": \"100x25X12cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"沥青\",\n" +
                "        \"QUANTITIES\": \"4\",\n" +
                "        \"mapkey\": \"沥青R3003100x25X12cmAC-10平方米\",\n" +
                "        \"sort\": \"02\",\n" +
                "        \"CZNAME\": \"AC-10\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"R3003\",\n" +
                "        \"repairtypename\": \"刨铺人工\",\n" +
                "        \"name\": \"切缝机切缝\",\n" +
                "        \"formula\": \"1*4\",\n" +
                "        \"SPECOLOR\": \"100x25X12cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"沥青\",\n" +
                "        \"QUANTITIES\": \"4\",\n" +
                "        \"mapkey\": \"沥青R3003100x25X12cmAC-10平方米\",\n" +
                "        \"sort\": \"03\",\n" +
                "        \"CZNAME\": \"AC-10\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"R3003\",\n" +
                "        \"repairtypename\": \"刨铺人工\",\n" +
                "        \"name\": \"洒底油\",\n" +
                "        \"formula\": \"1*4\",\n" +
                "        \"SPECOLOR\": \"100x25X12cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"沥青\",\n" +
                "        \"QUANTITIES\": \"4\",\n" +
                "        \"mapkey\": \"沥青R3003100x25X12cmAC-10平方米\",\n" +
                "        \"sort\": \"04\",\n" +
                "        \"CZNAME\": \"AC-10\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"R3003\",\n" +
                "        \"repairtypename\": \"刨铺人工\",\n" +
                "        \"name\": \"人工铺面层改性沥青砼5cm\",\n" +
                "        \"formula\": \"1*4\",\n" +
                "        \"SPECOLOR\": \"100x25X12cm\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"沥青\",\n" +
                "        \"QUANTITIES\": \"49\",\n" +
                "        \"mapkey\": \"沥青R3003平方米\",\n" +
                "        \"sort\": \"01\",\n" +
                "        \"CZNAME\": \"\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"R3003\",\n" +
                "        \"repairtypename\": \"刨铺人工\",\n" +
                "        \"name\": \"人工风炮凿挖沥青路面5cm外运 运距20km\",\n" +
                "        \"formula\": \"1*49\",\n" +
                "        \"SPECOLOR\": \"\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"沥青\",\n" +
                "        \"QUANTITIES\": \"49\",\n" +
                "        \"mapkey\": \"沥青R3003平方米\",\n" +
                "        \"sort\": \"02\",\n" +
                "        \"CZNAME\": \"\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"R3003\",\n" +
                "        \"repairtypename\": \"刨铺人工\",\n" +
                "        \"name\": \"切缝机切缝\",\n" +
                "        \"formula\": \"1*49\",\n" +
                "        \"SPECOLOR\": \"\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"沥青\",\n" +
                "        \"QUANTITIES\": \"49\",\n" +
                "        \"mapkey\": \"沥青R3003平方米\",\n" +
                "        \"sort\": \"03\",\n" +
                "        \"CZNAME\": \"\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"R3003\",\n" +
                "        \"repairtypename\": \"刨铺人工\",\n" +
                "        \"name\": \"洒底油\",\n" +
                "        \"formula\": \"1*49\",\n" +
                "        \"SPECOLOR\": \"\"\n" +
                "    },\n" +
                "    {\n" +
                "        \"CLASSTYPE\": \"沥青\",\n" +
                "        \"QUANTITIES\": \"49\",\n" +
                "        \"mapkey\": \"沥青R3003平方米\",\n" +
                "        \"sort\": \"04\",\n" +
                "        \"CZNAME\": \"\",\n" +
                "        \"UNITNAME\": \"平方米\",\n" +
                "        \"repairtypecode\": \"R3003\",\n" +
                "        \"repairtypename\": \"刨铺人工\",\n" +
                "        \"name\": \"人工铺面层改性沥青砼5cm\",\n" +
                "        \"formula\": \"1*49\",\n" +
                "        \"SPECOLOR\": \"\"\n" +
                "    }\n" +
                "]";
        List<Dict> dicts = JSONUtil.toList(s, Dict.class);
        return dicts;
    }
}
